import { query } from 'express';
import pool from '../config/db'
import { Items } from '../types/items'
import { ResultSetHeader, RowDataPacket } from 'mysql2/promise';

// 添加items 
export const addItems = async (items: Items): Promise<Items> => {
    const sql = `
        INSERT INTO items 
        (name, storage_location, production_date, expiry_date, near_expiry_days, notes, price, quantity, attributes, images, category_id, user_id, family_id, created_at, updated_at,status,attributes_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(),0,?)
    `;

    const values = [
        items.name,
        items.storage_location || null,
        items.production_date,
        items.expiry_date,
        items.near_expiry_days ?? null, // 使用 null 代替 undefined
        items.notes || null,
        items.price ?? null, // 使用 null 代替 undefined
        items.quantity,
        items.attributes || null,
        items.images || null,
        items.category_id ?? null, // 使用 null 代替 undefined
        items.user_id,
        items.family_id,
        items.attributes_id
    ];

    try {
        // 执行插入操作
        const [result] = await pool.execute<ResultSetHeader>(sql, values);

        // 获取插入后的 ID
        const insertedId = result.insertId;

        // 返回插入后的 item 对象
        return {
            ...items,
            id: insertedId,
            created_at: new Date(),
            updated_at: new Date()
        };
    } catch (error) {
        console.error('Error inserting item:', error);
        throw new Error('无法添加项目');
    }
};

// 根据指定参数查询items 获取详情数据
export const getItemsByParams = async (query: Partial<Items>, limit: number, offset: number): Promise<{ items: Items[]; total: number }> => {

    try {
        // 定义sql语句 
        let sql = 'SELECT * FROM items WHERE 1=1';
        let countSql = 'SELECT COUNT(*) as total FROM items WHERE 1=1';
        const values: any[] = [];
        const countValues: any[] = [];

        // 动态构建sql
        if (query.name) {
            sql += ' AND name LIKE ?';
            countSql += ' AND name LIKE ?';
            values.push(`%${query.name}%`);
            countValues.push(`%${query.name}%`);
        }
        if (query.near_expiry_days !== undefined) {
            sql += ' AND near_expiry_days = ?';
            countSql += ' AND near_expiry_days = ?';
            values.push(query.near_expiry_days);
            countValues.push(query.near_expiry_days);
        }
        if (query.price !== undefined) {
            sql += ' AND price = ?';
            countSql += ' AND price = ?';
            values.push(query.price);
            countValues.push(query.price);
        }
        if (query.quantity !== undefined) {
            sql += ' AND quantity = ?';
            countSql += ' AND quantity = ?';
            values.push(query.quantity);
            countValues.push(query.quantity);
        }
        if (query.attributes) {
            sql += ' AND attributes LIKE ?';
            countSql += ' AND attributes LIKE ?';
            values.push(`%${query.attributes}%`);
            countValues.push(`%${query.attributes}%`);
        }
        if (query.created_at) {
            sql += ' AND created_at = ?';
            countSql += ' AND created_at = ?';
            values.push(query.created_at);
            countValues.push(query.created_at);
        }
        if (query.updated_at) {
            sql += ' AND updated_at = ?';
            countSql += ' AND updated_at = ?';
            values.push(query.updated_at);
            countValues.push(query.updated_at);
        }
        if (query.category_id) {
            sql += ' AND category_id = ?';
            countSql += ' AND category_id = ?';
            values.push(query.category_id);
            countValues.push(query.category_id);
        }
        if (query.user_id) {
            sql += ' AND user_id = ?';
            countSql += ' AND user_id = ?';
            values.push(query.user_id);
            countValues.push(query.user_id);
        }
        if (query.family_id) {
            sql += ' AND family_id = ?';
            countSql += ' AND family_id = ?';
            values.push(query.family_id);
            countValues.push(query.family_id);
        }

        // 添加分页
        sql += ' LIMIT ? OFFSET ?';
        values.push(limit, offset);

        const [rows] = await pool.query<RowDataPacket[]>(sql, values);
        const [totalResult] = await pool.query<RowDataPacket[]>(countSql, countValues);
        // 获取总记录数
        const total = totalResult[0]?.total || 0;

        // 返回查询结果和总记录数
        return { items: rows as Items[], total };
    }
    catch (error) {
        console.error('Error querying items:', error);
        throw new Error('无法查询 items 数据');
    }
}

// 修改items数据
export const updateItem = async (id: number, updates: Partial<{
    name: string;
    storage_location: any;
    production_date: any;
    expiry_date: any;
    near_expiry_days: any;
    notes: any;
    price: any;
    quantity: any;
    attributes: any;
    created_at: any;
    updated_at: any;
    images: any;
    category_id: any;
    status: any;
}>): Promise<number> => {
    try {
        // 动态构建 SQL 和参数
        const fields: string[] = [];
        const values: any[] = [];

        Object.entries(updates).forEach(([key, value]) => {
            fields.push(`${key} = ?`);
            values.push(value);
        });
        if (!updates.updated_at || updates.updated_at === '') {
            fields.push(`updated_at = NOW()`);
        }

        if (fields.length === 0) {
            throw new Error('没有提供需要更新的字段');
        }

        // 构造 SQL 更新语句
        const sql = `UPDATE items SET ${fields.join(', ')} WHERE id = ?`;
        values.push(id); // 将 ID 添加到参数列表

        // 执行更新
        const [result] = await pool.execute(sql, values);

        // 返回受影响的行数
        return (result as any).affectedRows;
    } catch (error) {
        console.error('Error updating item:', error);
        throw new Error('无法修改 item 数据');
    }
};

// 获取items列表 根据不同的参数进行获取
export const getItemsList = async (
    query: Partial<Items>,
    limit: number,
    offset: number
): Promise<{ items: Items[]; total: number }> => {
    try {
        let sql = 'SELECT * FROM items WHERE family_id = ?';
        let countSql = 'SELECT COUNT(*) as total FROM items WHERE family_id = ?';
        const values: any[] = [query.family_id];
        const countValues: any[] = [query.family_id];

        // 验证 family_id 是否存在
        if (!query.family_id) {
            throw new Error('family_id 是必传参数');
        }
        console.log('服务层接收到的参数', query)
        // 动态构建 SQL 和参数
        if (query.attributes) {
            sql += ' AND attributes = ?'; // 精确匹配 attributes
            countSql += ' AND attributes = ?';
            values.push(query.attributes);
            countValues.push(query.attributes);
        }
        if (query.name) {
            sql += ' AND name LIKE ?';
            countSql += ' AND name LIKE ?';
            values.push(`%${query.name}%`);
            countValues.push(`%${query.name}%`);
        }
        if (query.price !== undefined) {
            sql += ' AND price = ?';
            countSql += ' AND price = ?';
            values.push(query.price);
            countValues.push(query.price);
        }

        // 添加分页
        sql += ' LIMIT ? OFFSET ?';
        values.push(limit, offset);
        console.log('查询参数', sql)
        // 执行查询
        const [rows] = await pool.query<RowDataPacket[]>(sql, values);
        const [totalResult] = await pool.query<RowDataPacket[]>(countSql, countValues);

        // 获取总记录数
        const total = totalResult[0]?.total || 0;

        // 处理结果
        const itemss = rows.map(row => ({
            id: row.id,
            name: row.name,
            storage_location: row.storage_location,
            production_date: row.production_date,
            expiry_date: row.expiry_date,
            price: row.price,
            quantity: row.quantity,
            attributes: row.attributes, // 包含 attributes 字段
            images: Array.isArray(row.images) ? row.images[0] : row.images, // 只取数组中的第一条数据
        }));

        // 返回查询结果和总记录数
        return { items: itemss as unknown as Items[], total };
    } catch (error) {
        console.error('Error querying items:', error);
        throw new Error('无法获取 items 数据');
    }
};

// 根据参数获取price的总值
export const getAllItemsPrice = async (query: Partial<Items>): Promise<{ items: Items[], totalPrice: number }> => {
    const { family_id, attributes } = query;

    if (!family_id) {
        throw new Error("family_id is required");
    }

    let sqlQuery = "SELECT price, quantity FROM items WHERE family_id = ?";
    const queryParams: any[] = [family_id];

    if (attributes) {
        sqlQuery += " AND attributes = ?";
        queryParams.push(attributes);
    }

    const [rows] = await pool.query<RowDataPacket[]>(sqlQuery, queryParams);

    if (!rows || rows.length === 0) {
        return { items: [], totalPrice: 0 }; // 如果没有数据，返回空数组和0的总价
    }

    // 计算总价
    const totalPrice = rows.reduce((sum: number, item: RowDataPacket) => {
        return sum + (item.price * item.quantity); // 计算每项的总价
    }, 0);

    const items = rows.map((row: RowDataPacket) => ({
        price: row.price,
        quantity: row.quantity
    }));
    return { items: items as unknown as Items[], totalPrice };
};

// 用户设置物品的（消耗预警阈值），根据阈值判断物品状态
export const setUserItemThreshold = async (
    itemId: number,
    threshold: number,
    familyId: string
): Promise<number> => {  // 修改返回类型为 number
    const sql = `
        INSERT INTO user_item_threshold (item_id, threshold, family_id)
        VALUES (?, ?, ?)
        ON DUPLICATE KEY UPDATE threshold = ?;
    `;
    try {
        const [result] = await pool.execute(sql, [itemId, threshold, familyId, threshold]);
        return (result as any).affectedRows;  // 返回 affectedRows，表示受影响的行数
    } catch (error) {
        console.error('Error updating item:', error);
        throw new Error('无法修改数据');
    }
};


// 获取某个物品的阈值（用于判断是否需要更改状态）
export const getItemThreshold = async (
    userId: number,
    itemId: number
): Promise<number> => {
    const sql = 'SELECT threshold FROM user_item_threshold WHERE user_id = ? AND item_id = ?';
    const [rows]: any = await pool.execute(sql, [userId, itemId]);

    // 如果找到了记录，返回阈值，否则返回默认阈值1
    return rows.length > 0 ? rows[0].threshold : 1;
};

// 更新物品数量并根据数量与阈值判断状态
export const updateItemQuantity = async (
    userId: number,
    itemId: number,
    newQuantity: number,
    familyId: number
): Promise<void> => {
    try {
        // 获取用户对该物品的阈值
        const threshold = await getItemThreshold(userId, itemId);

        let newStatus: number;

        if (newQuantity < threshold) {
            newStatus = 2; // 快要消耗完了
        } else if (newQuantity > threshold) {
            newStatus = 0; // 初始状态
        } else {
            newStatus = 1; // 默认状态
        }

        const sql = 'UPDATE items SET quantity = ?, status = ? WHERE id = ? AND family_id = ?';
        // 执行更新物品数量的SQL
        await pool.execute(sql, [newQuantity, newStatus, itemId, familyId]);
    } catch (error) {
        console.error('更新物品数量失败:', error);
        throw error;
    }
};